USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[ResultEkidenKukanKiroku]    スクリプト日付: 11/11/2024 11:58:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultEkidenKukanKiroku]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultEkidenKukanKiroku]
GO

CREATE PROCEDURE [dbo].[ResultEkidenKukanKiroku]					
	@TAIKAINO int,				
	@KYOGINO int,				
	@SUBNO int,				
    @ROUNDNO int,
	@KUMINO int,					
    @SOUJUN int, 
	@SYUMOKUNO int,
	@LANE int			
AS					
BEGIN

DECLARE @SQL nvarchar(max); 
DECLARE @PARA nvarchar(max); 
DECLARE @MIN nvarchar(max);  
DECLARE @MAX nvarchar(max);


SET @SQL = '
SELECT @MIN = MIN(ID)
	,  @MAX = MAX(ID)
FROM TBL_LAP
WHERE SYUMOKUNO = '+ STR(@SYUMOKUNO)

SET @PARA = '@MIN nvarchar(max) OUTPUT, @MAX nvarchar(max) OUTPUT';

EXECUTE sp_executesql @SQL, @PARA, @MIN OUTPUT, @MAX OUTPUT;


DECLARE @SQL2 nvarchar(max); 
DECLARE @PARA2 nvarchar(max); 
DECLARE @i int;
DECLARE @Advance int;
DECLARE @DATA int;
SET @i = 1;
SET @DATA = 0;
SET @SQL2 = '
WHILE @i < '+ STR(@SOUJUN)+'

BEGIN
  SELECT @Advance = (FIR.KIROKU + RSI.KIROKU)- RSI2.KIROKU
  FROM TBL_RESULT_SIGI AS RSI

  LEFT JOIN TBL_RESULT_SIGI AS RSI2
  ON RSI2.TAIKAINO = RSI.TAIKAINO
  AND RSI2.KYOGINO = RSI.KYOGINO
  AND RSI2.SUBNO = RSI.SUBNO
  AND RSI2.ROUNDNO = RSI.ROUNDNO
  AND RSI2.KUMINO = RSI.KUMINO
  AND RSI2.LANE = RSI.LANE

  LEFT JOIN TBL_RESULT_SIGI AS FIR
  ON FIR.TAIKAINO = RSI.TAIKAINO
  AND FIR.KYOGINO = RSI.KYOGINO
  AND FIR.SUBNO = RSI.SUBNO
  AND FIR.ROUNDNO = RSI.ROUNDNO
  AND FIR.KUMINO = RSI.KUMINO
  AND FIR.LANE = RSI.LANE
  AND FIR.SIGINO = 1

  WHERE RSI.TAIKAINO = '+STR(@TAIKAINO)+'
   AND RSI.KYOGINO ='+STR(@KYOGINO)+'
   AND RSI.SUBNO = '+STR(@SUBNO)+'
   AND RSI.ROUNDNO = '+STR(@ROUNDNO)+'
   AND RSI.KUMINO = '+STR(@KUMINO)+'
   AND RSI.LANE = '+STR(@LANE)+'
   AND RSI.SIGINO = (@i* 2)
   AND RSI2.SIGINO = (@i* 2)+1

SET @i = @i + 1
SET @DATA = @DATA + @Advance
END
'
SET @PARA2 = '@DATA INT OUTPUT, @i nvarchar(max) OUTPUT,@Advance INT OUTPUT';

EXECUTE sp_executesql @SQL2, @PARA2, @DATA OUTPUT, @i OUTPUT, @Advance OUTPUT;



DECLARE @SQL3 nvarchar(max); 
SET @SQL3 = '
SELECT RSI.TAIKAINO
			,  RSI.KYOGINO
			,  RSI.SUBNO
			,  RSI.ROUNDNO
			,  RSI.KUMINO
			,  RSI.LANE
			,  RSI.SIGINO 
			,  FIR.KIROKU AS FIRST '
IF @MIN = @SOUJUN
	BEGIN
--	  SET @SQL3 = @SQL3 +',  RSI.KIROKU AS KIROKU
--						  ,  TUKA.KIROKU AS TUKAKIROKU '
		SET @SQL3 = @SQL3 +',  CASE WHEN RSI.KIROKU = 0  THEN 0 ELSE RSI.KIROKU END AS KIROKU
												,  CASE WHEN TUKA.KIROKU = 0 THEN 0 ELSE TUKA.KIROKU END AS TUKAKIROKU '

						  
	END
ELSE IF @MAX = @SOUJUN
	BEGIN
--		SET @SQL3 = @SQL3 + ',  ((SIGI1.KIROKU + RSI.KIROKU)- RSI3.KIROKU ) AS KIROKU
--						     ,  RES.KIROKU AS TUKAKIROKU 
			SET @SQL3 = @SQL3 + ',  CASE WHEN RSI.KIROKU = 0 THEN 0 ELSE ((SIGI1.KIROKU + RSI.KIROKU)- RSI3.KIROKU ) END AS KIROKU
													 ,  CASE WHEN RES.KIROKU = 0 THEN 0 ELSE RES.KIROKU END AS TUKAKIROKU 
							 ,  (SIGI1.KIROKU + RSI2.KIROKU) AS TOTAL
							 ,  RSI3.KIROKU AS KEIKIROKU '
	END
ELSE
	BEGIN
--		SET @SQL3 = @SQL3 + ',((SIGI1.KIROKU + RSI.KIROKU)- RSI3.KIROKU ) AS KIROKU
--							 ,(RSI.KIROKU +'+STR(@DATA)+') AS TUKAKIROKU 
			SET @SQL3 = @SQL3 + ', CASE WHEN RSI.KIROKU = 0 THEN 0 ELSE ((SIGI1.KIROKU + RSI.KIROKU)- RSI3.KIROKU ) END AS KIROKU
													 , CASE WHEN RSI.KIROKU = 0 THEN 0 ELSE (RSI.KIROKU +'+STR(@DATA)+') END AS TUKAKIROKU 
							 ,  (SIGI1.KIROKU + RSI2.KIROKU) AS TOTAL
							 ,  RSI3.KIROKU AS KEIKIROKU '
	END
SET @SQL3 = @SQL3 + '
	
FROM TBL_RESULT_SIGI AS RSI 

LEFT JOIN TBL_RESULT AS RES
	ON RSI.TAIKAINO = RES.TAIKAINO
	AND RSI.KYOGINO = RES.KYOGINO
	AND RSI.SUBNO = RES.SUBNO
	AND RSI.ROUNDNO = RES.ROUNDNO
	AND RSI.KUMINO = RES.KUMINO
	AND RSI.LANE = RES.LANE

LEFT JOIN TBL_RESULT_SIGI AS TUKA
	ON RSI.TAIKAINO = TUKA.TAIKAINO
	AND RSI.KYOGINO = TUKA.KYOGINO
	AND RSI.SUBNO = TUKA.SUBNO	
	AND RSI.ROUNDNO = TUKA.ROUNDNO	
	AND RSI.KUMINO = TUKA.KUMINO
	AND RSI.LANE = TUKA.LANE
	AND TUKA.SIGINO = ('+STR(@SOUJUN)+' * 2)

LEFT JOIN TBL_RESULT_SIGI AS FIR
	ON RSI.TAIKAINO = FIR.TAIKAINO
	AND RSI.KYOGINO = FIR.KYOGINO
	AND RSI.SUBNO = FIR.SUBNO
	AND RSI.ROUNDNO = FIR.ROUNDNO
	AND RSI.KUMINO = FIR.KUMINO
	AND RSI.SIGINO = FIR.SIGINO
	AND FIR.EXTEND4 = 1'
	
	
IF @SOUJUN !=1
	BEGIN
	  SET @SQL3 = @SQL3 + '
		LEFT JOIN TBL_RESULT_SIGI AS SIGI1
			ON RSI.TAIKAINO = SIGI1.TAIKAINO
			AND RSI.KYOGINO = SIGI1.KYOGINO
			AND RSI.SUBNO = SIGI1.SUBNO	
			AND RSI.ROUNDNO = SIGI1.ROUNDNO	
			AND RSI.KUMINO = SIGI1.KUMINO
			AND RSI.LANE = SIGI1.LANE

		LEFT JOIN TBL_RESULT_SIGI AS RSI2
			ON RSI.TAIKAINO = RSI2.TAIKAINO
			AND RSI.KYOGINO = RSI2.KYOGINO
			AND RSI.SUBNO = RSI2.SUBNO	
			AND RSI.ROUNDNO = RSI2.ROUNDNO	
			AND RSI.KUMINO = RSI2.KUMINO
			AND RSI.LANE = RSI2.LANE

		LEFT JOIN TBL_RESULT_SIGI AS RSI3
			ON RSI.TAIKAINO = RSI3.TAIKAINO
			AND RSI.KYOGINO = RSI3.KYOGINO
			AND RSI.SUBNO = RSI3.SUBNO	
			AND RSI.ROUNDNO = RSI3.ROUNDNO	
			AND RSI.KUMINO = RSI3.KUMINO
			AND RSI.LANE = RSI3.LANE'
	END

SET @SQL3 = @SQL3 + '
  WHERE RSI.TAIKAINO = '+STR(@TAIKAINO)+'
   AND RSI.KYOGINO ='+STR(@KYOGINO)+'
   AND RSI.SUBNO = '+STR(@SUBNO)+'
   AND RSI.ROUNDNO = '+STR(@ROUNDNO)+'
   AND RSI.KUMINO = '+STR(@KUMINO)+'
   AND RSI.LANE = '+STR(@LANE)+'
   AND RSI.SIGINO = ('+STR(@SOUJUN)+' * 2)'



	IF @SOUJUN != 1
		BEGIN
			SET @SQL3 = @SQL3 + ' 
				AND SIGI1.SIGINO = 1
				AND RSI2.SIGINO = ('+STR(@SOUJUN)+' * 2)-2
				AND RSI3.SIGINO = ('+STR(@SOUJUN)+' * 2)-1'
		END
	SET @SQL3 = @SQL3 + 'ORDER BY RSI.LANE '

EXECUTE(@SQL3)
END